package org.compiere.process;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.model.MCMSContract;
import org.compiere.model.MTRMDeposit;
import org.compiere.model.MTRMReward;
import org.compiere.util.DB;
import org.compiere.util.Msg;

public class TRM_RewardFill extends SvrProcess {

	public int TRM_Deposit_ID = 0;
	public int CMS_Contract_ID = 0;
	public MCMSContract contract = null;
	
	public Properties m_ctx;
	public String trxName;
	
	private MTRMDeposit deposit;
	
	@Override
	protected void prepare() 
	{
		ProcessInfoParameter[] para = getParameter();
		
		for(int i = 0; i < para.length; i++) 
		{
			String name = para[i].getParameterName();			
			if (para[i].getParameter() == null);
			else if (name.equals("TRM_Deposit_ID"))
				TRM_Deposit_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else
			{
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
			}				
		}
		if(TRM_Deposit_ID==0)
			TRM_Deposit_ID = this.getRecord_ID();
		
		deposit = new MTRMDeposit(getCtx(), TRM_Deposit_ID, get_TrxName());
		
		m_ctx = getCtx();
		trxName = get_TrxName();
	}

	@Override
	protected String doIt() throws Exception 
	{
		deleteRewards(TRM_Deposit_ID);
		for(MCMSContract contract : getSupplement(TRM_Deposit_ID))
		{
			LoadRewardLines(TRM_Deposit_ID, contract.getBeginningDateExecution(), contract.getEndDateExecution(), deposit.getBankCondition(), contract.getCMS_Contract_ID());
		}
		return Msg.translate(m_ctx, "Success");
	}
		
	private void LoadRewardLines(int TRM_Deposit_ID, Timestamp startDate, Timestamp endDate, String daysInYearType, int CMS_Contract_ID)
	{
		//Prepare for calculate
		Calendar StartDate = Calendar.getInstance();
		StartDate.setTimeInMillis(startDate.getTime());

//		System.out.println("start date : "+new SimpleDateFormat("dd.MM.yyyy").format(startDate));
		
		Calendar EndDate = Calendar.getInstance();
		EndDate.setTimeInMillis(endDate.getTime());
//		System.out.println("end date : "+new SimpleDateFormat("dd.MM.yyyy").format(endDate));
		
		Calendar CurrentDate = Calendar.getInstance();
		CurrentDate.setTimeInMillis(startDate.getTime());
		
		Calendar tempDate = Calendar.getInstance();
		tempDate.setTimeInMillis(startDate.getTime());
		
		
		MTRMDeposit currDeposit = new MTRMDeposit(m_ctx, TRM_Deposit_ID, trxName);
		MCMSContract currContract = new MCMSContract(m_ctx, CMS_Contract_ID, trxName);
		BigDecimal incomingBalance = currContract.getContractSummary();
		int index = 0;
		int daysCountInYear = 365;
		BigDecimal withdrawal = new BigDecimal(0);
		BigDecimal AccruedInterest = new BigDecimal(0);
		
		if(currContract.getcms_contract_refill()==null || currContract.getcms_contract_removal()==null)
			throw new AdempiereException("Not set Removal/Refill on Contract - "+currContract.getCMS_Contract_ContractNum()+"\n"+currContract.getDescription());
		
//		System.out.println(currContract.getCMS_Contract_ContractNum());

//---------------------------------------------------------------------------------------------------------------------------------
		if(daysInYearType.equals("CalDay"))
		{
			if(currContract.getcms_contract_removal().equals("RD") && currContract.getcms_contract_refill().equals("FD"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
					index++;
					
					withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());							
					incomingBalance = incomingBalance.add(withdrawal);

					if(withdrawal.intValue()!=0)
						index=1;
					
					AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun()))
							.divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
		
					createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
					
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)==CurrentDate.getActualMaximum(Calendar.DAY_OF_MONTH))
						index=0;
					
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else if(currContract.getcms_contract_removal().equals("RN") && currContract.getcms_contract_refill().equals("FN"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
					index++;
					
					withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());							
					AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
							
					createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
					
					incomingBalance = incomingBalance.add(withdrawal);
					
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)==CurrentDate.getActualMaximum(Calendar.DAY_OF_MONTH) || withdrawal.intValue()!=0)
						index=0;
					
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else if(currContract.getcms_contract_removal().equals("RD") && currContract.getcms_contract_refill().equals("FN"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
					index++;
					
					withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());
					
					if(withdrawal.intValue()<0)
					{
						incomingBalance = incomingBalance.add(withdrawal);
						index=1;
						AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
		
						createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);						
					}
					else
					{
						AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);		
						createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
						
						incomingBalance = incomingBalance.add(withdrawal);
					}
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)==CurrentDate.getActualMaximum(Calendar.DAY_OF_MONTH) || withdrawal.intValue()>0)
						index=0;
					
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else if(currContract.getcms_contract_removal().equals("RN") && currContract.getcms_contract_refill().equals("FD"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
//					if(index==0 && CurrentDate.get(Calendar.DAY_OF_MONTH)==1)
//						System.out.println("--------------------------------------");
					
					index++;
					
					withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());
					
					if(withdrawal.intValue()>0)
					{
						incomingBalance = incomingBalance.add(withdrawal);
						index=1;
						AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);	
						createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);						
					}
					else
					{
						AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
						
						//
//						System.out.println(index+"  "
//								+new SimpleDateFormat("dd.MM.yyyy").format(CurrentDate.getTime())+"  "
//								+incomingBalance.intValue()+"  "
//								+withdrawal.intValue()+" "
//								+AccruedInterest.intValue()+" "
//								+currContract.getrateremun());	
						createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
						
						incomingBalance = incomingBalance.add(withdrawal);
					}
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)==CurrentDate.getActualMaximum(Calendar.DAY_OF_MONTH) || withdrawal.intValue()<0)
						index=0;
					
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else
			{
				throw new AdempiereException("Unknown Removal/Refill on Contract - "+currContract.getCMS_Contract_ContractNum()+"\n"+currContract.getDescription());
			}
		}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
		else if(daysInYearType.equals("IN360"))
		{
			daysCountInYear = 360;
			
			if(currContract.getcms_contract_removal().equals("RD") && currContract.getcms_contract_refill().equals("FD"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)<31)
					{						
						index++;
						withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());
						
						if(CurrentDate.get(Calendar.MONTH)==1 && CurrentDate.get(Calendar.DAY_OF_MONTH)==28)
						{
							for(int i = 28; i < 31; i++)
							{
								if(i==28 && withdrawal.intValue()!=0)
									index = 1;
								
								incomingBalance = incomingBalance.add(withdrawal);
								
								AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
								
								createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
								
								index++;
								withdrawal = new BigDecimal(0);
							}
							index = 0;
						}
						else
						{
							if(withdrawal.intValue()!=0)
								index = 1;
							
							incomingBalance = incomingBalance.add(withdrawal);
							
							AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
							
							createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
						}
						
						if(CurrentDate.get(Calendar.DAY_OF_MONTH)==30)
							index=0;
					}
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else if(currContract.getcms_contract_removal().equals("RN") && currContract.getcms_contract_refill().equals("FN"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)<31)
					{	
						index++;
						withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());
						
						if(CurrentDate.get(Calendar.MONTH)==1 && CurrentDate.get(Calendar.DAY_OF_MONTH)==28)
						{
							for(int i = 28; i < 31; i++)
							{
								
								AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
								
								createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
								
								incomingBalance = incomingBalance.add(withdrawal);
								
								if(i==28 && withdrawal.intValue()!=0)
									index = 0;
																
								withdrawal = new BigDecimal(0);
								index++;
							}
							
							index = 0;
						}
						else
						{							
							AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
							
							createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
							
							incomingBalance = incomingBalance.add(withdrawal);
							
							if(withdrawal.intValue()!=0)
								index = 0;
						}
						
						if(CurrentDate.get(Calendar.DAY_OF_MONTH)==30)
							index=0;						
					}
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else if(currContract.getcms_contract_removal().equals("RD") && currContract.getcms_contract_refill().equals("FN"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)<31)
					{						
						index++;
						withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());
						
						if(withdrawal.intValue()<0)
						{
							if(CurrentDate.get(Calendar.MONTH)==1 && CurrentDate.get(Calendar.DAY_OF_MONTH)==28)
							{
								for(int i = 28; i < 31; i++)
								{
									if(i==28 && withdrawal.intValue()!=0)
										index = 1;
									
									incomingBalance = incomingBalance.add(withdrawal);
									
									AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
									
									createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
									
									index++;
									withdrawal = new BigDecimal(0);
								}
								index = 0;
							}
							else
							{
								index = 1;	
								incomingBalance = incomingBalance.add(withdrawal);
								AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
								
								createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
							}					
						}
						else
						{
							if(CurrentDate.get(Calendar.MONTH)==1 && CurrentDate.get(Calendar.DAY_OF_MONTH)==28)
							{
								for(int i = 28; i < 31; i++)
								{
									
									AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
									
									createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
									
									incomingBalance = incomingBalance.add(withdrawal);
									
									if(i==28 && withdrawal.intValue()!=0)
										index = 0;
																	
									withdrawal = new BigDecimal(0);
									index++;
								}
								
								index = 0;
							}
							else
							{
								AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
								
								createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
								
								incomingBalance = incomingBalance.add(withdrawal);
								
								if(withdrawal.intValue()!=0)
									index = 0;
							}						
						}
						if(CurrentDate.get(Calendar.DAY_OF_MONTH)==30)
							index=0;
					}
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else if(currContract.getcms_contract_removal().equals("RN") && currContract.getcms_contract_refill().equals("FD"))
			{
				while(CurrentDate.before(EndDate)||CurrentDate.equals(EndDate))
				{
					if(CurrentDate.get(Calendar.DAY_OF_MONTH)<31)
					{
//						if(index==0 && CurrentDate.get(Calendar.DAY_OF_MONTH)==1)
//							System.out.println("--------------------------------------");
						
						index++;
						withdrawal = getStatementDifference(TRM_Deposit_ID, new Timestamp(CurrentDate.getTimeInMillis()), currDeposit.getC_BankAccount_ID());
						
						if(withdrawal.intValue()>0)
						{
							if(CurrentDate.get(Calendar.MONTH)==1 && CurrentDate.get(Calendar.DAY_OF_MONTH)==28)
							{
								for(int i = 28; i < 31; i++)
								{
									if(i==28 && withdrawal.intValue()!=0)
										index = 1;
									
									incomingBalance = incomingBalance.add(withdrawal);
									
									AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
									
									createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
									
									index++;
									withdrawal = new BigDecimal(0);
								}
								index = 0;
							}
							else
							{
								index = 1;	
								incomingBalance = incomingBalance.add(withdrawal);
								AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
								
								createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
							}					
						}
						else
						{
							if(CurrentDate.get(Calendar.MONTH)==1 && CurrentDate.get(Calendar.DAY_OF_MONTH)==28)
							{
								for(int i = 28; i < 31; i++)
								{
									
									AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
									
									createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
									
									incomingBalance = incomingBalance.add(withdrawal);
									
									if(i==28 && withdrawal.intValue()!=0)
										index = 0;
																	
									withdrawal = new BigDecimal(0);
									index++;
								}
								
								index = 0;
							}
							else
							{
								AccruedInterest = ((incomingBalance.multiply(currContract.getrateremun())).divide(new BigDecimal(daysCountInYear*100),BigDecimal.ROUND_UP)).multiply(new BigDecimal(index)).setScale(2,BigDecimal.ROUND_HALF_UP);
								
//								System.out.println(index+"  "
//								+new SimpleDateFormat("dd.MM.yyyy").format(CurrentDate.getTime())+"  "
//								+incomingBalance.intValue()+"  "
//								+withdrawal.intValue()+" "
//								+AccruedInterest.intValue()+" "
//								+currContract.getrateremun());
								
								createReaward(currDeposit.get_ID(), incomingBalance, new Timestamp(CurrentDate.getTimeInMillis()), index, currContract.getrateremun(), true, withdrawal, currDeposit.getC_Tax_ID(), AccruedInterest);
								
								incomingBalance = incomingBalance.add(withdrawal);
								
								if(withdrawal.intValue()!=0)
									index = 0;
							}						
						}
						if(CurrentDate.get(Calendar.DAY_OF_MONTH)==30)
							index=0;
					}
					CurrentDate.set(Calendar.DATE, CurrentDate.get(Calendar.DATE)+1);
				}
			}
			else
			{
				throw new AdempiereException("Unknown Removal/Refill on Contract - "+currContract.getCMS_Contract_ContractNum()+"\n"+currContract.getDescription());
			}
		}
//--------------------------------------------------------------------------------------------------------------------------------------------------------------------
		else
		{
			throw new AdempiereException("Unknown bank condition - "+daysInYearType);
		}
	}
	
	private MCMSContract[] getSupplement(int TRM_Deposit_ID) 
	{
		MTRMDeposit deposit = new MTRMDeposit(m_ctx, TRM_Deposit_ID,
				get_TrxName());
		int CMS_Contract_ID = deposit.getCMS_Contract_ID();
		String _sql = "SELECT CMS_Contract_ID FROM CMS_Contract " 
				+"WHERE deposits='Y' AND C_DocType_ID=1000067 "
				+ "AND CMS_Contract_Parent_ID = "+ CMS_Contract_ID
				+ " ORDER BY DateFrom";
		MCMSContract Contract = null;
		List<MCMSContract> list = new ArrayList<MCMSContract>();
		
		//add Main contract to list
		list.add(new MCMSContract(m_ctx, CMS_Contract_ID, get_TrxName()));
		//
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(_sql, get_TrxName());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Contract = new MCMSContract(m_ctx, rs.getInt(1), get_TrxName());
				list.add(Contract);
				Contract = null;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return list.toArray(new MCMSContract[list.size()]);
	}
	
	private int deleteRewards(int TRM_Deposit_ID) 
	{
		return DB.executeUpdate(
				"DELETE FROM TRM_Reward WHERE TRM_Deposit_ID = ?",
				TRM_Deposit_ID, trxName);
	}
	
//	private boolean haveBankStatement(int TRM_Deposit_ID, Timestamp DateAcct, int C_BankAccount_ID)
//	{
//		boolean isHave = false;
//		if(DB.getSQLValue(trxName, "SELECT COUNT(*) FROM C_BankStatement " +
//				"WHERE StatementDate = '"+DateAcct+"' " +
//				"AND C_BankAccount_ID = ? " +
//				"AND DocStatus = 'CO' AND BeginningBalance > 0", C_BankAccount_ID)>0)
//			isHave = true;
//		return isHave;
//	}
	
	private BigDecimal getStatementDifference(int TRM_Deposit_ID, Timestamp DateAcct, int C_BankAccount_ID)
	{
		BigDecimal retValue = null;
		retValue = DB.getSQLValueBD(trxName, "SELECT StatementDifference FROM C_BankStatement " +
				"WHERE StatementDate = '"+DateAcct+"' " +
				"AND C_BankAccount_ID = ? " +
				"AND DocStatus = 'CO' AND BeginningBalance > 0", C_BankAccount_ID);
		if(retValue==null)
			retValue = new BigDecimal(0);
		
		return retValue;
	}
	
	private void createReaward(int TRM_Deposit_ID, BigDecimal incomingBalance
						, Timestamp DateReward, int Index, BigDecimal InterestRate
							, boolean isPrediction, BigDecimal withdrawal, int C_Tax_ID, BigDecimal Premium)
	{
		MTRMReward reward = new MTRMReward(m_ctx, 0, trxName);
		reward.setTRM_Deposit_ID(TRM_Deposit_ID);
		reward.setIncomingBalance(incomingBalance);
		reward.setDateReward(DateReward);
		reward.setIndex(new BigDecimal(Index));
		reward.setInterestRate(InterestRate);
		reward.setisPrediction(true);
		reward.setwithdrawal(withdrawal);
		reward.setC_Tax_ID(C_Tax_ID);
		reward.setPremium(Premium);
		reward.saveEx();
	}
}
